using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;


namespace Nasa8x.Core
{
    public sealed class MainDB : IDisposable
    {

        private SqlConnection _connection;
        private SqlTransaction _transaction;
        private SqlConnectionStringBuilder _sbConnectionString;
        private readonly string ERROR_MESSAGE = "Hix Hix, your system error. Please, contact technical support: Y!M: nasa8x, nasavietnam. Email: nasavietnam@gmail.com. Website: nasa8x.com\n\nError detail: {0}";

        //IsolationLevel.ReadUncommitted

        #region Properties

        /// <summary>
        /// Returns the "one and only" instance object of this type. Singleton accessor property.
        /// </summary>
        /// 
        /// 
        /// 
        public static MainDB Instance
        {
            get
            {
                object objMain = HttpContext.Current.Session["__MainDB"];
                // double check synchronization pattern
                if (objMain == null)
                {

                    objMain = new MainDB();
                    HttpContext.Current.Session["__MainDB"] = objMain;

                }
                return (MainDB)objMain;
            }
        }

        //Disconnect Conn If you want
        public static void RemoveInstanse()
        {
            object objMain = HttpContext.Current.Session["__MainDB"];
            if (objMain != null)
            {
                Instance.Dispose();
                //items.Remove("__MainDB");

            }
        }



        //public static MainDB Instance
        //{
        //    get
        //    {
        //        IDictionary items = HttpContext.Current.Items;
        //        if (!items.Contains("__MainDB"))
        //        {
        //            lock (typeof(MainDB))
        //            {
        //                // double check synchronization pattern
        //                if (!items.Contains("__MainDB"))
        //                    items["__MainDB"] = new MainDB();
        //            }
        //        }

        //        return (MainDB)items["__MainDB"];
        //    }
        //}


        ////Disconnect Conn If you want
        //public static void RemoveInstanse()
        //{
        //    IDictionary _items = HttpContext.Current.Items;
        //    if (_items.Contains("__MainDB"))
        //    {
        //        Instance.Dispose();
        //        //items.Remove("__MainDB");

        //    }
        //}



        public SqlConnection Connection
        {
            get
            {
                if (_connection == null)
                    CreateConnection();
                return _connection;
            }

        }



        public string DataSource
        {
            get { return ConnectionString.DataSource; }
            set { ConnectionString.DataSource = value; }
        }
        public string UserId
        {
            get { return ConnectionString.UserID; }
            set { ConnectionString.UserID = value; }
        }

        public string Password
        {
            get { return ConnectionString.Password; }
            set { ConnectionString.Password = value; }
        }

        public string InitialCatalog
        {
            get { return ConnectionString.InitialCatalog; }
            set { ConnectionString.InitialCatalog = value; }
        }

        public SqlConnectionStringBuilder ConnectionString
        {

            
            get
            {
                if (_sbConnectionString == null)
                {
                    _sbConnectionString = new SqlConnectionStringBuilder();
                }
                return _sbConnectionString;

            }

            set
            {
                _sbConnectionString = value;

            }

        }

        #endregion

        #region Contructor
        public MainDB(string Server, string UserId, string Password)
        {


            SqlConnectionStringBuilder _strConn = new SqlConnectionStringBuilder
            {
                DataSource = Server,
                UserID = UserId,
                Password = Password,
                PersistSecurityInfo = false
            };

            ConnectionString = _strConn;

            CreateConnection();

        }


        public MainDB(string Server, string UserId, string Password, string Database)
        {


            SqlConnectionStringBuilder _strConn = new SqlConnectionStringBuilder
            {
                DataSource = Server,
                UserID = UserId,
                Password = Password,
                InitialCatalog = Database,
                PersistSecurityInfo = false
            };

            ConnectionString = _strConn;
            ///

            CreateConnection();

        }



        public MainDB(string StringConnection)
        {

            ConnectionString = new SqlConnectionStringBuilder(StringConnection);
            CreateConnection();

        }

        public MainDB(SqlConnectionStringBuilder _strConn)
        {



            ConnectionString = _strConn;
            CreateConnection();


        }

        public MainDB()
        {

            ConnectionStringSettings _conn = ConfigurationManager.ConnectionStrings["ConnectionString"];
            ConnectionString = new SqlConnectionStringBuilder(_conn.ConnectionString);

            CreateConnection();

        }

        #endregion


       

        #region Transaction Method

        /// <summary>
        /// Begins a new database transaction.
        /// </summary>
        /// <seealso cref="CommitTransaction"/>
        /// <seealso cref="RollbackTransaction"/>
        /// <returns>An object representing the new transaction.</returns>
        public SqlTransaction BeginTransaction()
        {
            CheckTransactionState(false);
            _transaction = Connection.BeginTransaction();
            return _transaction;
        }

        /// <summary>
        /// Begins a new database transaction with the specified 
        /// transaction isolation level.
        /// <seealso cref="CommitTransaction"/>
        /// <seealso cref="RollbackTransaction"/>
        /// </summary>
        /// <param name="isolationLevel">The transaction isolation level.</param>
        /// <returns>An object representing the new transaction.</returns>
        public SqlTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            CheckTransactionState(false);
            _transaction = Connection.BeginTransaction(isolationLevel);
            return _transaction;
        }

        /// <summary>
        /// Commits the current database transaction.

        /// <seealso cref="RollbackTransaction"/>
        /// </summary>
        public void CommitTransaction()
        {
            CheckTransactionState(true);
            _transaction.Commit();
            _transaction = null;
        }

        /// <summary>
        /// Rolls back the current transaction from a pending state.
        /// <seealso cref="CommitTransaction"/>
        /// </summary>
        public void RollbackTransaction()
        {
            CheckTransactionState(true);
            _transaction.Rollback();
            _transaction = null;
        }

        #endregion

        #region Check Transaction State

        /// <summary>
        /// Checks the state of the current transaction
        /// </summary>
        /// <param name="mustBeOpen"></param>
        private void CheckTransactionState(bool mustBeOpen)
        {
            if (mustBeOpen)
            {
                if (null == _transaction)
                    throw new InvalidOperationException("Transaction Not Open");
            }
            else
            {
                if (null != _transaction)
                    throw new InvalidOperationException("Transaction Is Open");
            }
        }

        #endregion


        /* Private Method */


        #region --Private Method

        private void CreateConnection()
        {
            try
            {
                _connection = new SqlConnection(ConnectionString.ConnectionString);
                _connection.Open();

            }
            catch (Exception ex)
            {

                throw new InvalidOperationException(string.Format(ERROR_MESSAGE, ex.Message));
            }


        }

        private static DataTable CreateDataTable(SqlCommand command)
        {
            DataTable dataTable = new DataTable();

            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }


        private static DataSet CreateDataSet(SqlCommand command)
        {
            DataSet _ds = new DataSet();
            new SqlDataAdapter(command).Fill(_ds);

            return _ds;
        }



        private SqlCommand CreateCommand(string sqlText, bool isProcedure)
        {

            SqlCommand cmd = Connection.CreateCommand();
            cmd.CommandText = sqlText;
            cmd.CommandTimeout = 0;
            cmd.Transaction = _transaction;
            if (isProcedure)
                cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        private SqlCommand CreateCommand(string sqlText)
        {
            return CreateCommand(sqlText, false);
        }


        protected DataTable CreateDataTable(IDbCommand command, int beginRow)
        {
            DataTable dataTable = new DataTable();
            //DataRow dr;
            int _row = 0;
            using (IDataReader reader = this.ExecuteReader((SqlCommand)command))
            {
                int columnCount = reader.FieldCount;
                for (int i = 0; i < columnCount; i++)
                {
                    dataTable.Columns.Add(reader.GetName(i), typeof(object));
                    //reader.GetDataTypeName()
                 


                }
                dataTable.BeginLoadData();
                object[] values = new object[columnCount];

                while (reader.Read())
                {

                    _row++;
                    if (_row > beginRow)
                    {
                        reader.GetValues(values);
                        dataTable.LoadDataRow(values, true);

                        //dr = dataTable.NewRow();
                        //for (int i = 0; i < columnCount; i++)
                        //{
                        //    dr[i] = reader[i].ToString();
                        //}
                        //dataTable.Rows.Add(dr);
                    }

                }

                dataTable.EndLoadData();


            }
            return dataTable;
        }
      
        private SqlCommand CreateStoreCommand(string nameOfStore, Hashtable parameters)
        {
            SqlCommand cmd = CreateCommand(nameOfStore, true);
            IDictionaryEnumerator enumerator = parameters.GetEnumerator();
            while (enumerator.MoveNext())
            {
                cmd.Parameters.Add(new SqlParameter("@" + enumerator.Key, enumerator.Value));
               
            }
            return cmd;
        }

        /// <summary>
        /// return SqlCommand instant
        /// </summary>
        /// <param name="nameOfStore">Name Of Store Procedure</param>
        /// <param name="_params">Store parameters</param>
        /// <returns>SqlCommand instant</returns>

        private SqlCommand CreateStoreCommand(string nameOfStore, params object[] _params)
        {

            string _sp = string.Empty;
            int i = 1;
            foreach (object _p in _params)
            {
                _sp += " '" + _p + "' ";

                _sp += ((i == 1) || (i == _params.Length)) ? "" : ",";

                i++;
            }

            SqlCommand cmd = CreateCommand("EXEC " + nameOfStore + _sp);
            return cmd;
        }




        private SqlCommand CreateGetCommand(string tableName, string whereSql, string orderBySql)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT * FROM [{0}]", tableName);

            if (!string.IsNullOrEmpty(whereSql))
                sql.Append(" WHERE " + whereSql);
            if (!string.IsNullOrEmpty(orderBySql))
                sql.Append(" ORDER BY " + orderBySql);
            return CreateCommand(sql.ToString());
        }

        private SqlCommand CreateGetCommand(string tableName, string whereSql, string orderBySql, int pageIndex, int pageSize)
        {

            if (pageSize <= 0)
                throw new InvalidCastException("PageSize Is Incorrect");

            if (string.IsNullOrEmpty(orderBySql))
                throw new InvalidCastException("Order By Not Set");

            const string _fields = "*";

            string _where = !string.IsNullOrEmpty(whereSql) ? (" WHERE " + whereSql) : "";
            int _totalCount = pageSize * pageIndex;
            int _prevCount = pageSize * (pageIndex - 1);
            string _sql = "SELECT * FROM (SELECT TOP {0} {1}, ROW_NUMBER()"
                    + " OVER ({2}) AS {3} FROM {4} {5}) AS {6} WHERE {3} < {7};";

            _sql = string.Format(_sql, _totalCount, _fields, orderBySql, "RowIndex",
                 tableName, _where, tableName + "_Temp", _prevCount);
            return CreateCommand(_sql);

        }

        private SqlCommand CreateGetCommand(string[] tables, string[] fields, string whereSql, string orderBySql, int pageIndex, int pageSize)
        {

            if (pageSize <= 0)
                throw new InvalidCastException("PageSize Is Incorrect");

            if (string.IsNullOrEmpty(orderBySql))
                throw new InvalidCastException("Order By Not Set");

            string _fields = (fields.Length > 0) ? string.Join(",", fields) : "*";

            string _tables;
            if (tables.Length > 0)
                _tables = string.Join(",", tables);
            else
            {
                throw new InvalidCastException("tables not set");
            }

            string _where = (!string.IsNullOrEmpty(whereSql)) ? (" WHERE " + whereSql) : "";

            int _totalCount = pageSize * pageIndex;
            int _prevCount = pageSize * (pageIndex - 1);
            string _sql = "SELECT * FROM (SELECT TOP {0} {1}, ROW_NUMBER()"
                    + " OVER ({2}) AS {3} FROM {4} {5}) AS {6} WHERE {3} < {7};";

            _sql = string.Format(_sql, _totalCount, _fields, orderBySql, "RowIndex",
                 _tables, _where, _tables.Replace(",", "_") + "_Temp", _prevCount);
            return CreateCommand(_sql);
        }


        private SqlCommand CreateGetCommand(string tableName, string[] fields, string whereSql, string orderBySql, int pageIndex, int pageSize)
        {

            if (pageSize <= 0)
                throw new InvalidCastException("PageSize Is Incorrect");

            if (string.IsNullOrEmpty(orderBySql))
                throw new InvalidCastException("Order By Not Set");

            string _fields = (fields.Length > 0) ? string.Join(",", fields) : "*";

            string _where = !string.IsNullOrEmpty(whereSql) ? (" WHERE " + whereSql) : "";
            int _totalCount = pageSize * pageIndex;
            int _prevCount = pageSize * (pageIndex - 1);
            string _sql = "SELECT * FROM (SELECT TOP {0} {1}, ROW_NUMBER()"
                    + " OVER ({2}) AS {3} FROM {4} {5}) AS {6} WHERE {3} < {7};";

            _sql = string.Format(_sql, _totalCount, _fields, orderBySql, "RowIndex",
                 tableName, _where, tableName + "_Temp", _prevCount);
            return CreateCommand(_sql);

        }

        private SqlCommand CreateGetCommand(string[] tables, string whereSql, string orderBySql)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM ");

            if (tables.Length > 0)
                sql.Append(string.Join(",", tables));
            else
            {
                throw new InvalidCastException("Tables Not Set");
            }

            if (!string.IsNullOrEmpty(whereSql))
                sql.Append(" WHERE " + whereSql);
            if (!string.IsNullOrEmpty(orderBySql))
                sql.Append(" ORDER BY " + orderBySql);
            return CreateCommand(sql.ToString());
        }


        private SqlCommand CreateGetCommand(string tableName, string[] fields, string whereSql, string orderBySql)
        {
            StringBuilder sql = new StringBuilder(2000);
            sql.Append("SELECT ");

            sql.Append((fields.Length > 0) ? string.Join(",", fields) : "*");

            sql.AppendFormat(" FROM [{0}]", tableName);

            if (!string.IsNullOrEmpty(whereSql))
                sql.Append(" WHERE " + whereSql);
            if (!string.IsNullOrEmpty(orderBySql))
                sql.Append(" ORDER BY " + orderBySql);
            return CreateCommand(sql.ToString());
        }


        private SqlCommand CreateGetCommand(string[] tables, string[] fields, string whereSql, string orderBySql)
        {
            StringBuilder sql = new StringBuilder(2000);
            sql.Append("SELECT ");

            sql.Append((fields.Length > 0) ? string.Join(",", fields) : "*");

            sql.Append(" FROM ");

            if (tables.Length > 0)
                sql.Append(string.Join(",", tables));
            else
            {
                throw new InvalidCastException("Tables Not Set");
            }

            if (!string.IsNullOrEmpty(whereSql))
                sql.Append(" WHERE " + whereSql);
            if (!string.IsNullOrEmpty(orderBySql))
                sql.Append(" ORDER BY " + orderBySql);
            return CreateCommand(sql.ToString());
        }

        #endregion




        //public void ExecuteProcedure(string nameOfStored, Hashtable _params)
        //{
        //    try
        //    {
        //        ExecuteNonQuery(CreateStoreCommand(nameOfStored, _params));

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new InvalidOperationException(string.Format(ERROR_MESSAGE, ex));

        //    }
        //}


        public object ExecuteProcedure(string nameOfStored, Hashtable _params)
        {
            try
            {
                return ExecuteScalar(CreateStoreCommand(nameOfStored, _params));

            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(string.Format(ERROR_MESSAGE, ex));

            }
        }

        

        public object ExecuteProcedure(string nameOfStored, params object[] _params)
        {
            try
            {
                return ExecuteScalar(CreateStoreCommand(nameOfStored, _params));

            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(string.Format(ERROR_MESSAGE, ex));

            }
        }




        public void ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                command.ExecuteNonQuery();
                return;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(string.Format(ERROR_MESSAGE, ex));

            }
        }


        public object ExecuteScalar(SqlCommand cmd)
        {
            // BeginTransaction();

            object _value = cmd.ExecuteScalar();

            //CommitTransaction();

            return _value;


        }

        public void AnotherNonQuery(string sqlText)
        {
            ExecuteNonQuery(CreateCommand(sqlText));
        }


        public SqlDataReader ExecuteReader(SqlCommand command)
        {
            try
            {
                return command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(string.Format(ERROR_MESSAGE, ex));

            }
        }


        #region  Backup And Restore

        public void Backup(string _outPath)
        {
            Backup(_outPath,null);
            
            
        }


        public void Backup(string _outPath,string _options)
         
        {
            StringBuilder _sb = new StringBuilder(2000);
            _sb.AppendLine("DECLARE	@DB SYSNAME;SET @DB=DB_NAME();");
            _sb.AppendFormat("BACKUP DATABASE @DB TO DISK =N'{0}'", _outPath);

            if (!String.IsNullOrEmpty(_options))
                _sb.AppendFormat(" WITH {0}",_options);
            	
            AnotherNonQuery(_sb.ToString());
        }


        #endregion

        #region Insert Data
        public void Insert(string tableName, Hashtable fieldsValues)
        {

            ICollection keys = fieldsValues.Keys;

            string _fieldsCol = "";
            string _fieldsValue = "";
            int _count = 0;
            foreach (string key in keys)
            {
                _fieldsCol += (((_count > 0) ? ", " : "")) + key;
                _fieldsValue += (((_count > 0) ? ", " : "")) + "@" + key;
                _count++;

            }

            string sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", tableName, _fieldsCol, _fieldsValue);


            using (SqlCommand cmd = CreateCommand(sql))
            {

                IDictionaryEnumerator enumerator = fieldsValues.GetEnumerator();
                while (enumerator.MoveNext())
                {
                    cmd.Parameters.Add(new SqlParameter("@" + enumerator.Key, enumerator.Value));
                }

                ExecuteNonQuery(cmd);


            }
        }


        #endregion

        #region Update Data

        public void Update(string tableName, Hashtable fieldsValues, string whereSql)
        {


            StringBuilder sql = new StringBuilder();
            ICollection keys = fieldsValues.Keys;
            sql.AppendFormat("UPDATE {0} SET ", tableName);
            int _count = 0;
            foreach (string key in keys)
            {

                sql.Append(((_count > 0) ? ", " : "") + key + " = @" + key);
                _count++;

            }

            if (!string.IsNullOrEmpty(whereSql))
                sql.Append(" WHERE " + whereSql);


            using (SqlCommand cmd = CreateCommand(sql.ToString()))
            {
                IDictionaryEnumerator enumerator = fieldsValues.GetEnumerator();
                while (enumerator.MoveNext())
                {
                    cmd.Parameters.Add(new SqlParameter("@" + enumerator.Key, enumerator.Value));
                }

                ExecuteNonQuery(cmd);
            }


        }


        #endregion

        #region Delete Data

        public void Delete(string tableName, string whereSql)
        {
            string sql = "DELETE FROM " + tableName;
            if (!string.IsNullOrEmpty(whereSql))
                sql += " WHERE " + whereSql;

            ExecuteNonQuery(CreateCommand(sql));
        }

        public void Delete(string tableName, string fieldName, ArrayList values)
        {
            if (values.Count > 0)
            {
                string strlistID = string.Join(",", (string[])values.ToArray(typeof(string)));

                string sql = "DELETE FROM " + tableName + " WHERE " + fieldName + " IN(" + strlistID + ")";
                ExecuteNonQuery(CreateCommand(sql));
            }

        }

        public void Delete(string tableName, string fieldName, int[] values)
        {
            if (values.Length > 0)
            {
                string strlistID = "";
                for (int i = 0; i < values.Length; i++)
                {
                    strlistID += ((i > 0) ? ", " : "") + values[i];
                }

                string sql = "DELETE FROM " + tableName + " WHERE " + fieldName + " IN(" + strlistID + ")";
                ExecuteNonQuery(CreateCommand(sql));
            }

        }

        #endregion

        #region Get Total Row

        public int GetTotalRow(string strTable, string whereSql)
        {
            string sql = "SELECT COUNT(1) AS TOTALROW FROM " + strTable;
            if (!string.IsNullOrEmpty(whereSql))
                sql += " WHERE " + whereSql;
            using (IDataReader reader = ExecuteReader(CreateCommand(sql)))
            {
                if (reader.Read())
                {
                    return Convert.ToInt32(reader["TOTALROW"]);
                }
                return 0;
            }
        }

        #endregion

        #region Get As DataTable

        public DataTable GetAsDataTable(string sqlScript, bool isStoreProcedure)
        {

            return CreateDataTable(CreateCommand(sqlScript, isStoreProcedure));
        }

        public DataTable GetAsDataTable(string nameOfStored)
        {
            return CreateDataTable(CreateStoreCommand(nameOfStored));
        }

        public DataTable GetAsDataTable(string nameOfStored, Hashtable parameters)
        {
            return CreateDataTable(CreateStoreCommand(nameOfStored, parameters));
        }


        public DataTable GetAsDataTable(string nameOfStored, params object[] _params)
        {
            return CreateDataTable(CreateStoreCommand(nameOfStored, _params));
        }

        public DataTable GetAsDataTable(string tableName, string whereSql, string orderBySql)
        {
            return CreateDataTable(CreateGetCommand(tableName, whereSql, orderBySql));
        }

        public DataTable GetAsDataTable(string tableName, string whereSql, string orderBySql, int pageIndex, int pageSize, out int pageCount)
        {

            int _itemCount = GetTotalRow(tableName, whereSql);

            if ((_itemCount % pageSize) > 0)
                pageCount = _itemCount / pageSize + 1;
            else
                pageCount = _itemCount / pageSize;

            return CreateDataTable(CreateGetCommand(tableName, whereSql, orderBySql, pageIndex, pageSize));
        }


        public DataTable GetAsDataTable(string[] tables, string whereSql, string orderBySql)
        {
            return CreateDataTable(CreateGetCommand(tables, whereSql, orderBySql));
        }

        public DataTable GetAsDataTable(string tableName, string[] fields, string whereSql, string orderBySql)
        {
            return CreateDataTable(CreateGetCommand(tableName, fields, whereSql, orderBySql));
        }

        public DataTable GetAsDataTable(string[] tables, string[] fields, string whereSql, string orderBySql)
        {
            return CreateDataTable(CreateGetCommand(tables, fields, whereSql, orderBySql));
        }


        #endregion

        #region GetAsDataSet
        public DataSet GetAsDataSet(string sqlScript, bool isStoreProcedure)
        {
            return CreateDataSet(CreateCommand(sqlScript, isStoreProcedure));
        }

        public DataSet GetAsDataSet(string nameOfStored)
        {
            return CreateDataSet(CreateStoreCommand(nameOfStored));
        }

        public DataSet GetAsDataSet(string nameOfStored, Hashtable parameters)
        {
            return CreateDataSet(CreateStoreCommand(nameOfStored, parameters));
        }


        public DataSet GetAsDataSet(string nameOfStored, params object[] _params)
        {
            return CreateDataSet(CreateStoreCommand(nameOfStored, _params));
        }


        public DataSet GetAsDataSet(string tableName, string whereSql, string orderBySql)
        {
            return CreateDataSet(CreateStoreCommand(tableName, whereSql, orderBySql));
        }


        public DataSet GetAsDataSet(string[] tables, string whereSql, string orderBySql)
        {
            return CreateDataSet(CreateGetCommand(tables, whereSql, orderBySql));
        }

        public DataSet GetAsDataSet(string tableName, string[] fields, string whereSql, string orderBySql)
        {
            return CreateDataSet(CreateGetCommand(tableName, fields, whereSql, orderBySql));
        }

        public DataSet GetAsDataSet(string[] tables, string[] fields, string whereSql, string orderBySql)
        {
            return CreateDataSet(CreateGetCommand(tables, fields, whereSql, orderBySql));
        }


        #endregion

        #region GetAs DataView
        public DataView GetAsDataView(string nameOfStored)
        {
            return new DataView(GetAsDataTable(nameOfStored));

        }
        public DataView GetAsDataView(string nameOfStored, bool IsStoredProcedure)
        {
            return new DataView(GetAsDataTable(nameOfStored, IsStoredProcedure));

        }

        public DataView GetAsDataView(string nameOfStored, Hashtable parameters)
        {
            return new DataView(GetAsDataTable(nameOfStored, parameters));
        }

        public DataView GetAsDataView(string nameOfStored, Hashtable parameters, int beginRow)
        {
            return new DataView(GetAsDataTable(nameOfStored, parameters, beginRow));
        }

        public DataView GetAsDataView(string nameOfStored, params object[] _params)
        {
            return new DataView(GetAsDataTable(nameOfStored, _params));
        }

        public DataView GetAsDataView(string nameOfStored, int beginRow, params object[] _params)
        {
            return new DataView(GetAsDataTable(nameOfStored, beginRow, _params));
        }

        public DataView GetAsDataView(string tableName, string whereSql, string orderBySql)
        {
            return new DataView(GetAsDataTable(tableName, whereSql, orderBySql));
        }

        public DataView GetAsDataView(string[] tables, string whereSql, string orderBySql)
        {
            return new DataView(GetAsDataTable(tables, whereSql, orderBySql));
        }

        public DataView GetAsDataView(string tableName, string[] fields, string whereSql, string orderBySql)
        {
            return new DataView(GetAsDataTable(tableName, fields, whereSql, orderBySql));
        }


        public DataView GetAsDataView(string tableName, string whereSql, string orderBySql, int beginRow)
        {
            return new DataView(GetAsDataTable(tableName, whereSql, orderBySql, beginRow));
        }


        public DataView GetAsDataView(string tableName, string[] fields, string whereSql, string orderBySql, int beginRow)
        {
            return new DataView(GetAsDataTable(tableName, fields, whereSql, orderBySql, beginRow));
        }

        public DataView GetAsDataView(string[] tables, string[] fields, string whereSql, string orderBySql)
        {
            return new DataView(GetAsDataTable(tables, fields, whereSql, orderBySql));
        }




        #endregion

        #region Get As DataReader
        public SqlDataReader GetAsDataReader(string nameOfStored)
        {
            return ExecuteReader(CreateCommand(nameOfStored, true));
        }

        public SqlDataReader GetAsDataReader(string nameOfStored, Hashtable parameters)
        {
            return ExecuteReader(CreateStoreCommand(nameOfStored, parameters));
        }

        public SqlDataReader GetAsDataReader(string nameOfStored, params object[] _params)
        {
            return ExecuteReader(CreateStoreCommand(nameOfStored, _params));
        }

        public SqlDataReader GetAsDataReader(string tableName, string whereSql, string orderBySql)
        {
            return ExecuteReader(CreateGetCommand(tableName, whereSql, orderBySql));
        }

        public SqlDataReader GetAsDataReader(string[] tables, string whereSql, string orderBySql)
        {
            return ExecuteReader(CreateGetCommand(tables, whereSql, orderBySql));
        }

        public SqlDataReader GetAsDataReader(string tableName, string[] fields, string whereSql, string orderBySql)
        {
            return ExecuteReader(CreateGetCommand(tableName, fields, whereSql, orderBySql));
        }


        public SqlDataReader GetAsDataReader(string[] tables, string[] fields, string whereSql, string orderBySql)
        {
            return ExecuteReader(CreateGetCommand(tables, fields, whereSql, orderBySql));
        }
        #endregion

        #region Get As PagedDataSource
        public PagedDataSource GetAsPagedDataSource(string nameOfStored)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(nameOfStored)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string sqlText, bool IsStoredProcedure)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(sqlText, IsStoredProcedure)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string nameOfStored, Hashtable parameters)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(nameOfStored, parameters)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string nameOfStored, Hashtable parameters, int beginRow)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(nameOfStored, parameters, beginRow)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string nameOfStored, params object[] _params)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(nameOfStored, _params)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string nameOfStored, int beginRow, params object[] _params)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(nameOfStored, beginRow, _params)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string tableName, string whereSql, string orderBySql)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(tableName, whereSql, orderBySql)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string[] tables, string whereSql, string orderBySql)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(tables, whereSql, orderBySql)
            };
        }

        public PagedDataSource GetAsPagedDataSource(string tableName, string[] fields, string whereSql, string orderBySql)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(tableName, fields, whereSql, orderBySql)
            };
        }



        public PagedDataSource GetAsPagedDataSource(string[] tables, string[] fields, string whereSql, string orderBySql)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(tables, fields, whereSql, orderBySql)
            };
        }


        public PagedDataSource GetAsPagedDataSource(string tableName, string[] fields, string whereSql, string orderBySql, int beginRow)
        {
            return new PagedDataSource
            {
                DataSource = GetAsDataView(tableName, fields, whereSql, orderBySql, beginRow)
            };
        }


        #endregion

        #region Dispose And Close

        public void Dispose()
        {
            Close();
            if (null != _connection)
                _connection.Dispose();
            GC.Collect();

        }

        public void Close()
        {
            if (null != _connection)
                _connection.Close();
        }

      

        #endregion




    }


}